﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BioModel;
using System.Data.SqlClient;

namespace BioDAL
{
    public class SalgSaedeDAL : ISalgSaede
    {
        #region Connection
        private SqlConnection sqlCn = null;

        public void OpenConnection(string connectionString)
        {
            Console.WriteLine("Connection Established");
            sqlCn = new SqlConnection();
            sqlCn.ConnectionString = connectionString;
            sqlCn.Open();
        }

        public void CloseConnection()
        {
            sqlCn.Close();
        }
        #endregion

        #region GetMaxID
        public int GetMaxId(string kolonneNavn, string tabel)
        {
            try
            {
                int mId = -1;
                string sql = "Select MAX(" + kolonneNavn + ") as maxID from " + tabel;
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Console.WriteLine(String.Format("{0}", dr["maxID"]));
                        mId = (int)(dr["maxID"]);
                    }
                    dr.Close();
                }
                return mId;
            }
            catch (SqlException se2)
            {
                Console.WriteLine("!SQL fejl " + se2);
                return -1;
            }
        }
        #endregion

        private static SalgSaede BuildSalgSaede(SqlDataReader dr)
        {
            SalgSaede sal = new SalgSaede((Salg)(dr["salgId"]), (Saede)(dr["saedeId"]));
            return sal;
        }

        public List<SalgSaede> GetAllSalgSaede()
        {
            List<SalgSaede> salgSaedeListe = new List<SalgSaede>();
            string sql = string.Format("Select * From bioSalgSaede");
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    salgSaedeListe.Add(BuildSalgSaede(dr));
                }
                dr.Close();
            }
            return salgSaedeListe;
        }

        public SalgSaede GetEnSalgSaede(int salgSaedeId)
        {
            SalgSaede salgSaede = new SalgSaede();
            string sql = string.Format("Select * from bioSalgSaede where salgSaedeId = {0}", salgSaedeId);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    salgSaede = (BuildSalgSaede(dr));

                }
                dr.Close();
            }
            return salgSaede;
        }

        public int InsertSalgSaede(int salgId, int saedeId)
        {
            try
            {
                string sql = string.Format("Insert into bioSalgSaede" +
                     "(salgId, saedeId) Values" +
                     "('{0}', '{1}')", salgId, saedeId);
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception se)
            {
                Console.WriteLine("!SQL Insert SalgSaede fejl: " + se);
            }
            return GetMaxId("salgId", "bioSalgSaede");
        }

        public int SletSalgSaede(int salgId)
        {
            int rc = -1;
            try
            {
                string sql = string.Format("DELETE FROM bioSalgSaede WHERE salgId = {0}", salgId);
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
                rc = 1;
                return rc;
            }
            catch (Exception)
            {
                return rc;
            }
        }
    }
}
